﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace SMART_VIET_V01.Lib
{
    class SQL_Functions
    {
        string conStr = "SERVER=.\\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=D:\\DKS\\SQLEx\\smartviet.mdf;User Instance=true";
        string conStr2 = "SERVER=.;DATABASE=smartviet;Trusted_Connection=True;";
        public SQL_Functions() {
            try
            {
                SqlConnection con = new SqlConnection(conStr);
                con.Open();
                con.Close();
            }catch(Exception ex){
                conStr = conStr2; 
            }
        }
        public DataTable returnDB(string query)
        {
            try
            {
                SqlConnection con = new SqlConnection(conStr);
                con.Open();
                SqlCommand cmd = new SqlCommand(query, con);
                SqlDataReader dr = cmd.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                con.Close();
                return dt;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return null; }
        }
        public int updateDK(string query, string colName, string colValue)
        {
            try
            {
                SqlConnection con = new SqlConnection(conStr);
                SqlCommand cmd = new SqlCommand(query, con);
                cmd.Parameters.AddWithValue(colName, colValue);
                con.Open();
                int y = cmd.ExecuteNonQuery();
                con.Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }

        public int SQLInsert(Hashtable _myHashtable, string table)
        {
            try
            {
                string keyString = null;
                string keyString2 = null;
                string querySQL = null;
                SqlConnection con = new SqlConnection(conStr);
                foreach (DictionaryEntry Item in _myHashtable)
                {
                    keyString += "[" + Item.Key.ToString() + "],";
                    keyString2 += "@" + Item.Key.ToString() + ",";
                }
                keyString = keyString.Substring(0, keyString.Length - 1);
                keyString2 = keyString2.Substring(0, keyString2.Length - 1);
                querySQL = "INSERT INTO " + table + "(" + keyString + ") values (" + keyString2 + ") ";
                SqlCommand cmd = new SqlCommand(querySQL, con);
                foreach (DictionaryEntry Item in _myHashtable)
                {
                    cmd.Parameters.AddWithValue("@" + Item.Key.ToString(), Item.Value.ToString());
                }
                con.Open();
                int y = cmd.ExecuteNonQuery();
                con.Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }

        public string returnID(string _table, string colID)
        {
            string query = "SELECT IDENT_CURRENT('" + _table + "') AS " + colID;
            DataTable dt = returnDB(query);
            if (dt != null) return dt.Rows[0][colID].ToString();
            else return null;
        }

        public int insertMedia(Hashtable _myHashtable, string table, string colVarbinary, string linkVarbinary)
        {
            try
            {
                string keyString = null;
                string keyString2 = null;
                string querySQL = null;
                SqlConnection con = new SqlConnection(conStr);
                foreach (DictionaryEntry Item in _myHashtable)
                {
                    keyString += "[" + Item.Key.ToString() + "],";
                    keyString2 += "@" + Item.Key.ToString() + ",";
                }
                keyString = keyString + "[" + colVarbinary + "]";
                keyString2 = keyString2 + "@" + colVarbinary;

                querySQL = "INSERT INTO " + table + "(" + keyString + ") values (" + keyString2 + ") ";
                SqlCommand cmd = new SqlCommand(querySQL, con);
                foreach (DictionaryEntry Item in _myHashtable)
                {
                    cmd.Parameters.AddWithValue("@" + Item.Key.ToString(), Item.Value.ToString());
                }
                cmd.Parameters.AddWithValue("@" + colVarbinary, StreamFile(linkVarbinary));
                con.Open();
                int y = cmd.ExecuteNonQuery();
                con.Close();
                return y;
            }
            catch (Exception ex) { Console.WriteLine(ex.Message); return 0; }
        }
        private byte[] StreamFile(string filename)
        {
            FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read);

            // Create a byte array of file stream length
            byte[] ImageData = new byte[fs.Length];

            //Read block of bytes from stream into the byte array
            fs.Read(ImageData, 0, System.Convert.ToInt32(fs.Length));

            //Close the File Stream
            fs.Close();
            return ImageData; //return the byte data
        }
    }
}
